/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Control;

import Database.DB;
import ExcelAccess.ExcelFormat;
import ExcelAccess.ExcelHandler;
import Message.LoginMessage;
import Message.QueryMessage;
import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.ObjectInputStream;
import java.io.ObjectOutputStream;
import java.io.OutputStream;
import java.net.Socket;
import java.sql.SQLException;
import java.util.ArrayList;

/**
 *
 * @author liyuchen
 */
public class ClientThread extends Thread{
    public Socket clientSocket = null;
    public LoginMessage loginMsn = null;
    private boolean sessionFinish;
    private DB clientDB = null;
    private File userDir = null;
    private int fileIdx = 0;
    private static final int FILE_IDX_LIMIT = 1000;
    public long startTime = 0;
    QueryMessage newQuery;
       
    public ClientThread(Socket clientSocket,LoginMessage loginMsn){
        setThreadStatus(false);
        this.startTime = System.currentTimeMillis();
        this.clientSocket = clientSocket;
        this.loginMsn = loginMsn;
        newQuery = null;
    }
    
    public synchronized void setThreadStatus(boolean sessionFinish){
        this.sessionFinish = sessionFinish;
    }
    
    public boolean isThreadFinish(){
        return this.sessionFinish;
    }
    
    public void login(){
        clientDB = new DB();
        try {
            clientDB.connectToDB(loginMsn);  
        } catch (Exception ex) {
            try{
                System.out.println(ex.getMessage());
                loginMsn.SetLoginResult(false,2);
                loginMsn.loginErrorMsn += ex.getMessage();
                getOutStream().writeObject(loginMsn);
            }catch(Exception e){}
            setThreadStatus(true);
            return;
        }
        
        userDir = new File("users\\"+loginMsn.username);
        if(!userDir.exists()){
            boolean result = userDir.mkdir();
            if(!result){
                loginMsn.SetLoginResult(false,3);
            }else{
                loginMsn.SetLoginResult(true,0);
            }
        }else{
            loginMsn.SetLoginResult(true,0);
        }

        try {
            getOutStream().writeObject(loginMsn);
        } catch (IOException ex) {
            setThreadStatus(true);
        }
    }
    
    /**
     * @return true if query continues, otherwise terminate thread
     */
    public boolean queryHandler(){
        newQuery.FormatFields();
        String query ="";
        switch (newQuery.queryType) {
            case QueryMessage.TERMINATE:
                return false;
            case QueryMessage.GENERATE_REPOT:
            {
                String fileName = userDir.getAbsolutePath() + "\\";
                File f = new File(fileName+"generated_report.xlsx");
                try {
                    while (f.exists()) {
                        f = new File(fileName+"-"+fileIdx+"-"+"generated_report.xlsx");
                        if(fileIdx > FILE_IDX_LIMIT){
                            newQuery.SetQueryResult(3, null);
                            return true;
                        }
                        fileIdx++;
                    }
                    f.createNewFile();
                } catch (IOException ex) {
                    newQuery.SetQueryResult(4, null);
                    newQuery.message += ex.getMessage();
                    f.delete();
                    return true;
                }
                
                ArrayList<String> objectContent = null;
                ArrayList<String> serviceCallsContent = null;
                ExcelHandler fileWriter = null;
                try {
                    objectContent = clientDB.getObjectInfo(newQuery.tableName);
                    serviceCallsContent = clientDB.getObjectAllServiceCall(newQuery.tableName);
                    fileWriter = new ExcelHandler(objectContent,serviceCallsContent);
                    fileWriter.writeContent(f);
                } catch (Exception ex) {
                    newQuery.SetQueryResult(2, null);
                    newQuery.message += ex.getMessage();
                    f.delete();
                    return true;
                }
                
                byte[] fbuffer = new byte[(int)f.length()];
                BufferedInputStream fileStream = null;
                try {
                    fileStream = new BufferedInputStream(new FileInputStream(f));
                    fileStream.read(fbuffer, 0, (int) f.length());
                    fileStream.close();
                } catch (IOException ex) {
                    newQuery.SetQueryResult(10, null);
                    newQuery.message += ex.getMessage();
                    f.delete();
                    return true;
                }
                newQuery.SetQueryResult(0, null);
                newQuery.fileLength = f.length();
                
                try {
                    this.getOutStream().writeObject(newQuery);
                    this.getOutStream().writeObject(fbuffer);
                    newQuery = (QueryMessage) this.getInputStream().readObject();
                } catch (Exception ex) {
                    f.delete();
                    return false;
                }
                
                newQuery.SetQueryResult(0, null);
                f.delete();
                return true;
            }
            case QueryMessage.TRANSFER_BACK:
            {
                File file = new File(newQuery.fileName);
                if(!file.exists()){
                    newQuery.SetQueryResult(9, null);
                    return true;
                }
                //String fileName = "\""+newQuery.fileName+"\"";
                //File file = new File(userDir.getAbsolutePath()+ "\\report.doc");
//                while (file.exists()) {
//                    file = new File(userDir.getAbsolutePath()+ "\\" + fileIdx + "-report.doc");
//                    if (fileIdx > FILE_IDX_LIMIT) {
//                        newQuery.SetQueryResult(10, null);
//                        return true;
//                    }
//                    fileIdx++;
//                }
                
//                Runtime rt = Runtime.getRuntime();
//                try {
//                    Process pr = rt.exec("cmd /C copy " + fileName + " " + file.getAbsolutePath());
//                    pr.waitFor();
//                } catch (Exception ex) {
//                    newQuery.SetQueryResult(9, null);
//                    newQuery.message += ex.getMessage();
//                    file.delete();
//                    return true;
//                }
                
                byte[] fbuffer = new byte[(int)file.length()];
                BufferedInputStream fileStream = null;
                try {
                    fileStream = new BufferedInputStream(new FileInputStream(file));
                    fileStream.read(fbuffer, 0, (int) file.length());
                    fileStream.close();
                } catch (IOException ex) {
                    newQuery.SetQueryResult(10, null);
                    newQuery.message += ex.getMessage();
                    return true;
                }
                newQuery.SetQueryResult(0, null);
                newQuery.fileLength = file.length();
                
                try {
                    this.getOutStream().writeObject(newQuery);
                    this.getOutStream().writeObject(fbuffer);
                    newQuery = (QueryMessage) this.getInputStream().readObject();
                } catch (Exception ex) {
                    return false;
                }
                
                newQuery.SetQueryResult(0, null);
                return true;
            }
            case QueryMessage.TRANSFER:
            {
                String fileName = userDir.getAbsolutePath() + "\\";
                File f = new File(fileName+newQuery.fileName);
                BufferedOutputStream outStream = null;
                try {
                    while (f.exists()) {
                        f = new File(fileName+"-"+fileIdx+"-"+newQuery.fileName);
                        if(fileIdx > FILE_IDX_LIMIT){
                            newQuery.SetQueryResult(3, null);
                            return true;
                        }
                        fileIdx++;
                    }
                    f.createNewFile();
                    outStream = new BufferedOutputStream(new FileOutputStream(f));
                } catch (IOException ex) {
                    newQuery.SetQueryResult(4, null);
                    newQuery.message += ex.getMessage();
                    f.delete();
                    return true;
                }
                try {
                    this.getOutStream().writeObject(newQuery);
                } catch (IOException ex) {
                    f.delete();
                    return false;
                }
                
                
                byte[] buffer;
                // write to file
                try {
                    buffer = (byte[])(this.getInputStream().readObject());
                    outStream.write(buffer,0,(int)newQuery.fileLength);
                    outStream.close();
                } catch (Exception e) {
                    newQuery.SetQueryResult(5, null);
                    newQuery.message += e.getMessage();
                    f.delete();
                    return true;
                }
                
                ExcelHandler fileReader = new ExcelHandler(f.getAbsolutePath());
                // read excel file
                try {
                    fileReader.readContent();
                } catch (Exception ex) {
                    newQuery.SetQueryResult(6,null );
                    newQuery.message += ex.getMessage();
                    f.delete();
                    return true;
                }
                
                boolean objectNumberExist;
                boolean objectExist;
                try {
                    objectNumberExist = clientDB.isObjectNumberExist(fileReader.objectContent.get(0));
                } catch (SQLException ex) {
                    newQuery.SetQueryResult(2, null);
                    newQuery.message += ex.getMessage();
                    f.delete();
                    return true;
                }
                if(!objectNumberExist){
                    newQuery.SetQueryResult(7, null);
                    try{
                        this.getOutStream().writeObject(newQuery);
                        newQuery = (QueryMessage) this.getInputStream().readObject();
                    }catch(Exception e){
                        f.delete();
                        return false;
                    }
                    if(newQuery.queryType == QueryMessage.CANCEL){
                        f.delete();
                        newQuery.SetQueryResult(0, null);
                        return true;
                    }else{
                        try {
                            clientDB.addObject(fileReader.objectContent);
                        } catch (SQLException ex) {
                            newQuery.SetQueryResult(2, null);
                            newQuery.message += ex.getMessage();
                            f.delete();
                            return true;
                        }
                    }
                }else{
                    try {
                        objectExist = clientDB.isObjectExist(fileReader.objectContent);
                    } catch (SQLException ex) {
                        newQuery.SetQueryResult(2, null);
                        newQuery.message += ex.getMessage();
                        f.delete();
                        return true;
                    }
                    if (!objectExist){
                        newQuery.SetQueryResult(8, null);
                        try {
                            this.getOutStream().writeObject(newQuery);
                            newQuery = (QueryMessage) this.getInputStream().readObject();
                        } catch (Exception e) {
                            f.delete();
                            return false;
                        }
                        if (newQuery.queryType == QueryMessage.CANCEL) {
                            newQuery.SetQueryResult(0, null);
                            f.delete();
                            return true;
                        }else{
                            try {
                                clientDB.updateObject(fileReader.objectContent);
                            } catch (SQLException ex) {
                                newQuery.SetQueryResult(2, null);
                                newQuery.message += ex.getMessage();
                                f.delete();
                                return true;
                            }
                        }
                    }
                }    
                fileReader.serviceCallsContent = ExcelFormat.addBackSlashSQL(fileReader.serviceCallsContent);
                try {
                    clientDB.addServiceCallsBulk(fileReader.serviceCallsContent);
                } catch (SQLException ex) {
                    newQuery.SetQueryResult(2, null);
                    newQuery.message += ex.getMessage();
                    f.delete();
                    return true;
                }
                newQuery.SetQueryResult(0, null);
                f.delete();
                return true;
            }
            case QueryMessage.SEARCH:
                query = "Select ";
                for (int i = 0; i < newQuery.outputFields.size(); i++) {
                    query += newQuery.outputFields.get(i);
                    if (i != newQuery.outputFields.size() - 1) {
                        query += ",";
                    } else {
                        query += " ";
                    }
                }
                query += " from " + newQuery.tableName;
                query = prepareConstraint(query, newQuery);
                try {
                    newQuery.SetQueryResult(0, clientDB.searchDB(query, newQuery.outputFields.size()));
                } catch (SQLException ex) {
                    newQuery.SetQueryResult(2, null);
                    newQuery.message += ex.getMessage();
                } catch (Exception ex) {
                    newQuery.SetQueryResult(1, null);
                    return false;
                }
                return true;
            case QueryMessage.GENERAL_SEARCH:
                query = "Select * from " + newQuery.tableName;
                query = prepareConstraint(query, newQuery);
                try {
                    newQuery.SetQueryResult(0, clientDB.searchDB(query, newQuery.outputFields.size()));
                } catch (SQLException ex) {
                    newQuery.SetQueryResult(2, null);
                    newQuery.message += ex.getMessage();
                } catch (Exception ex) {
                    newQuery.SetQueryResult(1, null);
                    return false;
                }
                return true;
            case QueryMessage.UPDATE:
                if(newQuery.updateFields.size() == 0) return true;
                query = "Update " + newQuery.tableName + " set ";
                for(int i = 0; i < newQuery.updateFields.size();i++){
                    query += newQuery.updateFields.get(i) + " = ";
                    if(newQuery.updateValues.get(i).isEmpty()){
                        query += "null";
                    }
                    else if(newQuery.updateType.get(i).intValue()== QueryMessage.STRING_TYPE){
                        query += "\'" + newQuery.updateValues.get(i) + "\'";
                    }else{
                        query += newQuery.updateValues.get(i);
                    }
                    if (i != newQuery.updateFields.size() - 1) {
                        query += ",";
                    }
                }
                query = prepareConstraint(query,newQuery);
                break;
            case QueryMessage.INSERT:
                query = "insert into " + newQuery.tableName;
                query += " values(";
                for(int i = 0; i < newQuery.updateValues.size(); i++) {
                    if(newQuery.updateValues.get(i).isEmpty()){
                        query += "null";
                    }
                    else if (newQuery.updateType.get(i).intValue() == QueryMessage.STRING_TYPE) {
                        query += "\'" + newQuery.updateValues.get(i) + "\'";
                    } else {
                        query += newQuery.updateValues.get(i);
                    }
                    if (i != newQuery.updateValues.size() - 1) query += ",";
                }
                query += ");";
                break;
            case QueryMessage.DELETE:
                query = "delete from " + newQuery.tableName;
                query = prepareConstraint(query,newQuery);
                break;
            
                
        }
        
        try {
            clientDB.updateDB(query);
        } catch (SQLException ex) {
            newQuery.SetQueryResult(2, null);
            newQuery.message += ex.getMessage();
        } catch (Exception ex) {
            newQuery.SetQueryResult(1, null);
            return false;
        }
        return true;
    }
    
    private String prepareConstraint(String query,QueryMessage newQuery){
        String queryRes = query;
        if(newQuery.constraintFields == null){
            queryRes += ";";
            return queryRes;
        }
        else if(newQuery.constraintFields.isEmpty()){
            queryRes += ";";
            return queryRes;
        }
        queryRes += " where ";
        
        int nonEmptyElement = ExcelFormat.getNumNonEmptyElement(newQuery.constraintValues);
        int counter = 0;
        
        
        for (int i = 0; i < newQuery.constraintFields.size(); i++) {
            if(newQuery.constraintValues.get(i).isEmpty()) continue;
            counter++;
            queryRes += newQuery.constraintFields.get(i);
            if(newQuery.isFuzzSearch == null){
                queryRes += " = ";
            }
            else if(newQuery.isFuzzSearch.get(i).booleanValue() == true) {
                queryRes += " like ";
            } else {
                queryRes += " = ";
            }
            if (newQuery.constraintType.get(i).intValue() == QueryMessage.STRING_TYPE) {
                queryRes += "\'";
                if(newQuery.isFuzzSearch == null)
                    queryRes += newQuery.constraintValues.get(i);
                else if(newQuery.isFuzzSearch.get(i).booleanValue() == true)
                    queryRes += "%" + newQuery.constraintValues.get(i) +"%";
                else
                    queryRes += newQuery.constraintValues.get(i);
                queryRes += "\'";
            } else {
                queryRes += newQuery.constraintValues.get(i);
            }
            if (counter != nonEmptyElement) {
                if(newQuery.queryType == QueryMessage.GENERAL_SEARCH)
                    queryRes += " or ";
                else
                    queryRes += " and ";
            } else {
                queryRes += ";";
            }
        }
        return queryRes;
    }
    
    @Override
    public void run() {
        login();
        if (isThreadFinish()) return;
        while (true) {
            try{
                 newQuery = (QueryMessage) getInputStream().readObject();
            } catch (Exception e) {
                break;
            }
            this.fileIdx = 0;
            boolean alive = queryHandler();
            try {
                getOutStream().writeObject(newQuery);
            } catch (IOException ex) {
                break;
            }
            if(!alive) break;
        }
        System.out.println(this.loginMsn.username + " finish ");
        setThreadStatus(true);
    }

     private synchronized ObjectOutputStream getOutStream() throws IOException{
         OutputStream os = clientSocket.getOutputStream();  
         ObjectOutputStream oos = new ObjectOutputStream(os);
         return oos;
     }
     
     private synchronized ObjectInputStream getInputStream() throws IOException{
         InputStream is = clientSocket.getInputStream();  
         ObjectInputStream ois = new ObjectInputStream(is); 
         return ois;
     } 
     
     public synchronized void closeConnection() throws IOException{
         this.clientSocket.close();
     }
}
